﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;

namespace Sys.Common
{
    /// <summary>
    /// Nopi电子表格处理
    /// </summary>
    public static class NopiHelper
    {
        #region 导出
        /// <summary>
        /// 导出到Excex
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="columns">导出字段</param>
        /// <param name="list">实体列表</param>
        public static void Export<T>(List<string[]> columns, List<T> list)
        {
            HSSFWorkbook book = new HSSFWorkbook();

            ISheet sheet = book.CreateSheet("sheet");
            IRow row = null;
            ICell cell = null;

            #region 创建标题

            row = sheet.CreateRow(0);
            row.Height = 500;

            ICellStyle style = book.CreateCellStyle();

            IFont font = book.CreateFont();
            font.Boldweight = 800;
            style.SetFont(font);

            style.Alignment = HorizontalAlignment.CENTER;
            style.VerticalAlignment = VerticalAlignment.CENTER;

            for (int i = 0; i < columns.Count; i++)
            {
                var head = row.CreateCell(i);
                head.CellStyle = style;
                head.SetCellValue(columns[i][1]);
            }

            #endregion

            #region 创建内容

            for (int i = 0; i < list.Count; i++)
            {
                row = sheet.CreateRow(i + 1);
                row.Height = 500;

                for (int j = 0; j < columns.Count; j++)
                {
                    cell = row.CreateCell(j);

                    ICellStyle cellStyle = book.CreateCellStyle();

                    cellStyle.Alignment = HorizontalAlignment.CENTER;
                    cellStyle.VerticalAlignment = VerticalAlignment.CENTER;

                    object drValue = typeof(T).GetProperty(columns[j][0]).GetValue(list[i], null);

                    if (drValue != null)
                    {
                        switch (drValue.GetType().ToString())
                        {
                            case "System.String":
                                cell.SetCellValue(drValue.ToString()); break;
                            case "System.DateTime":
                                IDataFormat format = book.CreateDataFormat();
                                cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
                                cell.SetCellValue(Convert.ToDateTime(drValue)); break;
                            case "System.Boolean":
                                cell.SetCellValue(Convert.ToBoolean(drValue)); break;
                            case "System.Int32":
                                cell.SetCellValue(Convert.ToInt32(drValue)); break;
                            case "System.Decimal":
                                cell.SetCellValue(Convert.ToDouble(drValue)); break;
                            case "System.Double":
                                cell.SetCellValue(Convert.ToDouble(drValue)); break;
                            case "System.DBNull":
                                cell.SetCellValue(string.Empty); break;
                            default: cell.SetCellValue(string.Empty); break;
                        }
                    }
                    else { cell.SetCellValue(string.Empty); }

                    cell.CellStyle = cellStyle;
                }
            }

            for (int i = 0; i < columns.Count; i++)
            {
                sheet.AutoSizeColumn(i);
                sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 500);
            }

            #endregion

            MemoryStream ms = new MemoryStream();
            //写到内存中
            book.Write(ms);

            #region 输出文件

            string filename = StringHelper.GetGuid() + ".xls";

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.ClearHeaders();
            HttpContext.Current.Response.Buffer = false;

            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
            HttpContext.Current.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
            HttpContext.Current.Response.AddHeader("Content-Transfer-Encoding", "binary");

            HttpContext.Current.Response.ContentType = "application/octet-stream";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
            HttpContext.Current.Response.BinaryWrite(ms.ToArray());

            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.End();

            #endregion
        }
        #endregion

        #region 导入
        /// <summary>读取excel     
        /// 默认第一行为表头，导入第一个工作表  
        /// </summary>     
        /// <param name="strFileName">excel文档路径</param>     
        /// <returns></returns>     
        public static DataTable Import(string strFileName)
        {
            DataTable dt = new DataTable();

            HSSFWorkbook hssfworkbook;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            IRow headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;

            for (int j = 0; j < cellCount; j++)
            {
                ICell cell = headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }

            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }

        /// <summary>  
        /// 从Excel中获取数据到DataTable  
        /// </summary>  
        /// <param name="strFileName">Excel文件全路径(服务器路径)</param>  
        /// <param name="SheetName">要获取数据的工作表名称</param>  
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>  
        /// <returns></returns>  
        public static DataTable RenderDataTableFromExcel(string strFileName, string SheetName, int HeaderRowIndex)
        {
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = new HSSFWorkbook(file);
                return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
            }
        }

        /// <summary>  
        /// 从Excel中获取数据到DataTable  
        /// </summary>  
        /// <param name="strFileName">Excel文件全路径(服务器路径)</param>  
        /// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>  
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>  
        /// <returns></returns>  
        public static DataTable RenderDataTableFromExcel(string strFileName, int SheetIndex, int HeaderRowIndex)
        {
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = new HSSFWorkbook(file);
                string SheetName = workbook.GetSheetName(SheetIndex);
                return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
            }
        }

        /// <summary>  
        /// 从Excel中获取数据到DataTable  
        /// </summary>  
        /// <param name="ExcelFileStream">Excel文件流</param>  
        /// <param name="SheetName">要获取数据的工作表名称</param>  
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>  
        /// <returns></returns>  
        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
        {
            IWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
            ExcelFileStream.Close();
            return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
        }

        /// <summary>  
        /// 从Excel中获取数据到DataTable  
        /// </summary>  
        /// <param name="ExcelFileStream">Excel文件流</param>  
        /// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>  
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>  
        /// <returns></returns>  
        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)
        {
            IWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
            ExcelFileStream.Close();
            string SheetName = workbook.GetSheetName(SheetIndex);
            return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
        }

        /// <summary>  
        /// 从Excel中获取数据到DataTable  
        /// </summary>  
        /// <param name="workbook">要处理的工作薄</param>  
        /// <param name="SheetName">要获取数据的工作表名称</param>  
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>  
        /// <returns></returns>  
        public static DataTable RenderDataTableFromExcel(IWorkbook workbook, string SheetName, int HeaderRowIndex)
        {
            ISheet sheet = workbook.GetSheet(SheetName);
            DataTable table = new DataTable();
            try
            {
                IRow headerRow = sheet.GetRow(HeaderRowIndex);
                int cellCount = headerRow.LastCellNum;

                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }

                int rowCount = sheet.LastRowNum;

                #region 循环各行各列,写入数据到DataTable
                for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell == null)
                        {
                            dataRow[j] = null;
                        }
                        else
                        {
                            //dataRow[j] = cell.ToString();  
                            switch (cell.CellType)
                            {
                                case CellType.BLANK:
                                    dataRow[j] = null;
                                    break;
                                case CellType.BOOLEAN:
                                    dataRow[j] = cell.BooleanCellValue;
                                    break;
                                case CellType.NUMERIC:
                                    dataRow[j] = cell.ToString();
                                    break;
                                case CellType.STRING:
                                    dataRow[j] = cell.StringCellValue;
                                    break;
                                case CellType.ERROR:
                                    dataRow[j] = cell.ErrorCellValue;
                                    break;
                                case CellType.FORMULA:
                                default:
                                    dataRow[j] = "=" + cell.CellFormula;
                                    break;
                            }
                        }
                    }
                    table.Rows.Add(dataRow);
                    //dataRow[j] = row.GetCell(j).ToString();  
                }
                #endregion
            }
            catch (System.Exception ex)
            {
                table.Clear();
                table.Columns.Clear();
                table.Columns.Add("出错了");
                DataRow dr = table.NewRow();
                dr[0] = ex.Message;
                table.Rows.Add(dr);
                return table;
            }
            finally
            {
                //sheet.Dispose();  
                workbook = null;
                sheet = null;
            }
            #region 清除最后的空行
            for (int i = table.Rows.Count - 1; i > 0; i--)
            {
                bool isnull = true;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (table.Rows[i][j] != null)
                    {
                        if (table.Rows[i][j].ToString() != "")
                        {
                            isnull = false;
                            break;
                        }
                    }
                }
                if (isnull)
                {
                    table.Rows[i].Delete();
                }
            }
            #endregion
            return table;
        }
        #endregion
    }
}
